/**
 * The contents of this file are subject to the license and copyright detailed in the LICENSE and NOTICE files at the root of the source tree and available online at
 * http://www.askosi.org/license.html
 */
package be.destin.skos.core;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericKeyedObjectPool;
import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Logger;

import be.destin.util.DatabaseAdapter;
import be.destin.util.RunCommand;
import be.destin.util.SupportedDatabases;
import be.destin.util.Util;

public class SqlPool extends ConnectionPool {

	/** log4j category */
	private static Logger log = Logger.getLogger(SqlPool.class);

	private DatabaseAdapter dbAdapter;

	public static SqlPool getSqlPool(String name) {
		return (SqlPool) openedPools.get(name);
	}

	public Connection getConnection() {
		try {
			return DriverManager.getConnection("jdbc:apache:commons:dbcp:" + name);
		} catch (SQLException ex) {
			log.error("Connection get for Pool=" + name + ",  error:", ex);
			Util.printStack(ex);
			return null;
		}
	}

	/*
	 * freeConnection should be called for every connection got.
	 * It returns null to assign it to the caller connection and release it completely.
	 */
	public static Connection freeConnection(Connection con) {
		try {
			if (con != null) con.close();
		} catch (SQLException ex) {
			log.error("Connection close,  error:", ex);
			Util.printStack(ex);
		}
		return null;
	}

	public static synchronized Connection getSqlConnection(String configFile, String name) {
	    Connection con = null;
	    name = name.trim();
		if (openedPools.containsKey(name)) {
			try {
				con = DriverManager.getConnection("jdbc:apache:commons:dbcp:"+ name);
			} catch (SQLException e) {
			   log.error("Get connection from pool "+name+" for "+configFile+": "+e.toString());	
			}
			return con;
		}
		String poolConfig = configFile;
		SqlPool pool = null;
		FileInputStream is = null;
		try {
			// Open pool configuration file in subdirectory "pool"
			poolConfig = (new File(configFile)).getCanonicalFile().getParent()+File.separator
			+ name + "-pool.cfg";
			is = new FileInputStream(poolConfig);
			pool = new SqlPool();
			pool.properties = new Properties();
			pool.properties.load(is);
		} catch ( FileNotFoundException e ) {
			log.error("Read connection configuration from pool "+name+" in "+poolConfig+": "+e.toString());
		} catch ( IOException e ) {
			log.error("Read connection configuration from pool "+name+" in "+poolConfig+": "+e.toString());
		} finally {
			RunCommand.closeAble(is,"configuration for pool "+name+" in "+poolConfig);
		}
		if (pool == null) return null;
	
		try {
			// Parameters for DB accesses
	
			pool.dbURL = pool.properties.getProperty("url"); // URL for
			// connecting to
			// database, e.g.
			// jdbc:postgresql://localhost:5432/dspace
	
			pool.dbAdapter = new DatabaseAdapter(pool.properties.getProperty("type","postgresql"));
			
			String dbDriver = pool.properties.getProperty("driver",pool.dbAdapter.getDriver()); // default JDBC Driver,
			// com.mysql.jdbc.Driver
			// sun.jdbc.odbc.JdbcOdbcDriver
			// connect.microsoft.MicrosoftDriver
			// oracle.jdbc.driver.OracleDriver

			// Oracle has a slightly different validation query
			// SELECT 1 FROM DUAL
			pool.validationQuery = pool.properties.getProperty("validation",
					pool.dbAdapter.getValidationQuery());
	
			pool.dbUsername = pool.properties.getProperty("username");
			pool.dbPassword = pool.properties.getProperty("password");
			//pool.maxStatements = pool.properties.getProperty("maxStatements");
	
			// Register basic JDBC driver
			Class<?> driverClass = Class.forName(dbDriver);
			Driver basicDriver = (Driver) driverClass.newInstance();
			DriverManager.registerDriver(basicDriver);
	
			// Read pool configuration parameter or use defaults
			// Note we check to see if property is null; getIntProperty
			// returns
			// '0' if the property is not set OR if it is actually set to
			// zero.
			// But 0 is a valid option...
			pool.maxConnections = Util.string2int(pool.properties
					.getProperty("maxconnections"), 30);
			pool.maxWait = Util.string2int(pool.properties.getProperty("maxwait"),
					15000);
			pool.maxIdle = Util.string2int(pool.properties.getProperty("maxidle"), -1);
			boolean useStatementPool = Util.string2bool(pool.properties
					.getProperty("statementpool"), true);
	
			// Create object pool
			ObjectPool connectionPool = new GenericObjectPool(null, // PoolableObjectFactory
					// - set below
					pool.maxConnections, // max connections
					GenericObjectPool.WHEN_EXHAUSTED_BLOCK,
					pool.maxWait, // don't block more than 5 seconds
					pool.maxIdle, // max idle connections (unlimited)
					true, // validate when we borrow connections from pool
					false // don't bother validation returned connections
			);
	
			// ConnectionFactory the pool will use to create connections.
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
					pool.dbURL, pool.dbUsername, pool.dbPassword);
	
			//
			// Now we'll create the PoolableConnectionFactory, which wraps
			// the "real" Connections created by the ConnectionFactory with
			// the classes that implement the pooling functionality.
	
			GenericKeyedObjectPoolFactory statementFactory = null;
			if (useStatementPool) {
				// The statement Pool is used to pool prepared statements.
				GenericKeyedObjectPool.Config statementFactoryConfig = new GenericKeyedObjectPool.Config();
				// Just grow the pool size when needed.
				// 
				// This means we will never block when attempting to
				// create a query. The problem is unclosed statements,
				// they can never be reused. So if we place a maximum
				// cap on them, then we might reach a condition where
				// a page can only be viewed X number of times. The
				// downside of GROW_WHEN_EXHAUSTED is that this may
				// allow a memory leak to exist. Both options are bad,
				// but I'd prefer a memory leak over a failure.
				//
				// FIXME: Perhaps this decision should be derived from
				// config parameters?
				statementFactoryConfig.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
	
				statementFactory = new GenericKeyedObjectPoolFactory(null,
						statementFactoryConfig);
			}
	
			//contains connectionPool.setFactory(this) ...
			new PoolableConnectionFactory(
					connectionFactory, connectionPool, statementFactory,
					pool.validationQuery, // validation query
					false, // read only is not default for now
					false);
	
			//
			// Finally, we create the PoolingDriver itself...
			//
			PoolingDriver driver = new PoolingDriver();
	
			//
			// ...and register our pool with it.
			//
			driver.registerPool(name, connectionPool);
			pool.setName(name);
			return DriverManager.getConnection("jdbc:apache:commons:dbcp:"
					+ name);
	
		} catch (java.sql.SQLException ex) {
			log.error(name + ", SQL connection exception: ", ex);
			return null;
		} catch (Exception e) {
			// Need to be able to catch other exceptions. Pretend they are
			// SQLExceptions, but do log
			log.warn("Exception initializing jdbc:apache:commons:dbcp:" + name,
					e);
			return null;
		}
	}

	public Map<String,String> evalMap(String query) {
		if (query == null || query.length()==0) return null;

		Map<String,String> aMap = null;
		Connection con = getConnection();
		if (con != null) {
			log.debug(query);
			// create and execute a SELECT
			Statement stmt = null;
			ResultSet rs = null;
			try {
				stmt = con.createStatement();
				rs = stmt.executeQuery(query);

				aMap = new HashMap<String,String>();
				while (rs.next()) {
					String currCode = rs.getString("key");
					if (currCode == null || currCode.length() == 0) {
						log
						.error(query
								+ ": key field is not returned by SQL query");
						break;
					}
					String currValue = rs.getString("value");
					if (currValue == null) {
						log
						.error(query
								+ ": value field is not returned by SQL query");
						break;
					}
					aMap.put(currCode, currValue);
				}
			} catch (SQLException ex) {
				log.error(query + " : ", ex);
				Util.printStack(ex);
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
				}
			}
			freeConnection(con);
		}
		return aMap;
	}
}
